<?php
function printYear($date)
{
	return "<td class=\"EventReportYear\" colspan=\"4\">".$date->format("Y")."</td></tr><tr>";
}

function printMonth($date)
{
	return "<td class=\"EventReportMonth\" colspan=\"4\">".$date->format("F")."</td></tr><tr>";
}

function printDay($date)
{
	return "<td class=\"EventReportDay\" colspan=\"4\">".$date->format("j l")."</td></tr><tr>";
}

function compare1($a,$b)
{
//	print_r ($a)."\n";
	if (new DateTime($a['Start']) == new DateTime($b['Start'])) 
	{
		return 0;
	}
	return (new DateTime($a['Start']) < new DateTime($b['Start'])) ? -1 : 1;
}

function getAddresses($group)
{
	$addresses = array();
	switch ($group)
	{
		case 1:	//men
			array_push($addresses,"paulbanks60@yahoo.co.uk");
			array_push($addresses,"chris@enticknap.co.uk");
			array_push($addresses,"MGrave@doosanbabcock.com");
			array_push($addresses,"michaelgrave@clockburndrift.freeserve.co.uk");
			array_push($addresses,"austin_gunn@hotmail.com");
			array_push($addresses,"anthony_p_harbottle@yahoo.co.uk");
			array_push($addresses,"mrjmusical@aol.com");
			array_push($addresses,"willxlawrence@googlemail.com");
			array_push($addresses,"john.lewis@ncl-coll.ac.uk");
			array_push($addresses,"thelewises@blueyonder.co.uk");
			array_push($addresses,"david.molyneux@ncl.ac.uk");
			array_push($addresses,"rowland@f2s.com");
			array_push($addresses,"4lanrowland@gmail.com");
			array_push($addresses,"m.carey.smith@btinternet.com");
			array_push($addresses,"richard.pinkstone@gmail.com");
			array_push($addresses,"neilturnbull16@hotmail.com");
			array_push($addresses,"shaunturnbull@hotmail.com");

			array_push($addresses,"girlschoirdirector@stnicnewcastle.co.uk");
			array_push($addresses,"directorofmusic@stnicnewcastle.co.uk");
			array_push($addresses,"jordan_2212@hotmail.co.uk");
			break;
		case 2:	//boys
			array_push($addresses,"tanya_lymar@yahoo.co.uk");
			array_push($addresses,"ivanlymar@googlemail.com");
			array_push($addresses,"dianabell@btinternet.com");
			array_push($addresses,"r.a.e.coningham@durham.ac.uk");
			array_push($addresses,"p.coningham@rgs.newcastle.sch.uk");
			array_push($addresses,"saravenning@btinternet.com");
			array_push($addresses,"jevening@ybs.co.uk");
			array_push($addresses,"l.pearson3@sky.com");
			array_push($addresses,"baxendaleclan@aol.com");
			array_push($addresses,"gbadecharity@yahoo.co.uk");
			array_push($addresses,"carver.se@pg.com");
			array_push($addresses,"lcorbin@blueyonder.co.uk");
			array_push($addresses,"ncartmell@aol.com");
			array_push($addresses,"Acartmell@aol.com");
			array_push($addresses,"karenmeeson@blueyonder.co.uk");
			array_push($addresses,"stephenmeeson@blueyonder.co.uk");
			array_push($addresses,"lyle.armstrong@ncl.ac.uk");
			array_push($addresses,"majlinda.lako@newcastle.ac.uk");
			array_push($addresses,"caroline.austin@ncl.ac.uk");
			array_push($addresses,"raj@rajjy.com");
			array_push($addresses,"shilpakanakala@yahoo.co.in");
			array_push($addresses,"drssuresh@hotmail.com");
			
			array_push($addresses,"girlschoirdirector@stnicnewcastle.co.uk");
			array_push($addresses,"directorofmusic@stnicnewcastle.co.uk");
			array_push($addresses,"jordan_2212@hotmail.co.uk");
			break;
		case 3: //girls
			array_push($addresses,"elisabeth@michaelstoddart.com");
			array_push($addresses,"michael@michaelstoddart.org");
			array_push($addresses,"lydia@michaelstoddart.org");
			array_push($addresses,"dean@stnicnewcastle.co.uk");
			array_push($addresses,"michelleacd@hotmail.co.uk");
			array_push($addresses,"sue_thornton@yahoo.co.uk");
			array_push($addresses,"Richard.Dodd@northumberland.gov.uk");
			array_push($addresses,"mike@newtonmill.com");
			array_push($addresses,"saralcat@yahoo.co.uk");
			array_push($addresses,"chrisjlowes@yahoo.co.uk");
			array_push($addresses,"alisonarusso@btinternet.com");
			array_push($addresses,"catherinerusso@rocketmail.com");
			array_push($addresses,"emily@michaelstoddart.com");
			array_push($addresses,"pestokids@googlemail.com");
			array_push($addresses,"alexvandenbos@btinternet.com");
			array_push($addresses,"michaelnorton@ireland.com");
			array_push($addresses,"helennorton@ireland.com");
			array_push($addresses,"Rebecca.h.watts@blueyonder.co.uk");
			array_push($addresses,"r.browell@browells.co.uk");
			array_push($addresses,"philip.browell@browells.co.uk");
			array_push($addresses,"awoej@hotmail.co.uk");
			array_push($addresses,"gunnar.oma@btinternet.com");
			array_push($addresses,"celia@michaelstoddart.com");
			array_push($addresses,"c.john@blueyonder.co.uk");

			array_push($addresses,"girlschoirdirector@stnicnewcastle.co.uk");
			array_push($addresses,"directorofmusic@stnicnewcastle.co.uk");
			array_push($addresses,"jordan_2212@hotmail.co.uk");
			break;
	}
	return $addresses;
}

function mailGroup($database, $group)
{
	
	$users= new Enumeration($database->Recopy(),'SnapUsers');
	$array = $users->getArray();
//	print_r($array);

	$groups = new Enumeration($database->recopy(),'EventInvolveeNames');
	$groupsarray = $groups->getArray();
//	print_r($array);
	
	$dtNow = new DateTime("now");
	$valueNow = $dtNow->format("Y-m-d H:i");
	$dtThen = new DateTime("now");
	$dtThen->modify('-1 week');
	$valueThen = $dtThen->format("Y-m-d H:i");
	$sql = "SELECT Ev.*,Inv.EventInvolveeNameID FROM Events as Ev INNER JOIN EventInvolvees AS Inv ON Ev.EventID = Inv.EventID WHERE Ev.Start > '".$valueNow."' AND (Ev.DeletedBy = '' OR Ev.CreatedWhen > '".$valueThen."' OR Ev.DeletedWhen > '".$valueThen."') AND Inv.EventInvolveeNameID = '".$group."' ORDER BY Start";
//	echo $sql."\n";

	$database->ExecuteSQL($sql);
	
//	echo $database->getNumRows()." rows<br>\n";
	
	$messageHTML = "";
	$messageHTML .= "<h1>".$groupsarray[$group]." Schedule</h1>\n";
	$messageHTML .= "<p>Correct on ".$dtNow->format("F j, Y")."</p>\n";
	$messageHTML .= "<p>Deleted events are shown in <font color=\"red\">red</font>. <br>Modified events are shown in <font color=\"Orange\">orange</font>. <br>New events are shown in <font color=\"green\">green</font>.</p>";
	
	$messageHTML .= "<table>\n";
	
	$previousDate = new DateTime("-10 years");
	
	$rowArray = array();
	while($row = $database->getRow())
	{
		array_push($rowArray,$row);
	}
	
		// Show as deleted if Deleted and no subsequent events have originalID (!=0) set to either the first originalID or to the first eventID)
		// Show as created if Created and originalID = 0
		// Show as modified if Created and originalID != 0 and no later eventIDs with same originalID
		// Then print by Start
		
//	print_r ($rowArray)."\n";
	/*
	foreach($rowArray as $row)
	{
		echo $row['EventID']." ".$row['OriginalRecordID']." ".$row['Start']." ".$row['DeletedBy']."<br>\n";
	}
	
	echo "<br>\n------------------------<br>\n";
	*/
	
	$resultArray = array();
	
	reset($rowArray);
	//$aRow=current($rowArray);
	$i=0;
	while($aRow=current($rowArray))
	{
	
	//	echo "<br>\n";
	//	echo "EventID: ".$aRow['EventID']." ".$aRow['DeletedBy']."<br>\n";
		
		if ($aRow['DeletedBy']!=0)
		{
			$eventID = $aRow['EventID'];
			$originalID = $aRow['OriginalRecordID'];
			$n=0;
			while(1)
			{
//				echo $aRow['CreatedWhen']."<br>\n";
				if(($aRow=next($rowArray))===false)
				{
					// go back to it
					reset($rowArray);
					$aRow=current($rowArray);
					for($j=0;$j<$i;$j++)$aRow=next($rowArray);
					
					$aRow['Type']=($aRow['DeletedBy']!=0)?"Deleted":"Modified";
					array_push($resultArray,$aRow);
	//				print_r($aRow)."<br>\n";
	//				echo "No match on ".$aRow['EventID']." => ".$aRow['Type']." - Break<br>\n";
					// go back to next one
					break;
				}
	
	//			echo $aRow['EventID']." ".$eventID." ".$originalID." ".$aRow['OriginalRecordID']."<br>\n";
				if(
					(($originalID == $aRow['OriginalRecordID'])&& ($originalID != 0))
				||
					($eventID == $aRow['OriginalRecordID'])
				)
				{
					//discount this one so go back to the next element
					break;
				}
				$n++;
			}
			reset($rowArray);
			for($j=0;$j<$i;$j++)$aRow=next($rowArray);
		}
		else // created
		{
			if($aRow['OriginalRecordID']==0)
			{
				$aRow['Type']=(new DateTime($aRow['CreatedWhen'])>$dtThen)?"New":"Old";
//				echo $aRow['CreatedWhen']." ".$valueThen." ".$aRow['Type']."<br>\n";
				array_push($resultArray,$aRow);
			}
			else
			{
				$originalID = $aRow['OriginalRecordID'];
				while(1)
				{
					if(($aRow=next($rowArray))===false)
					{
						
						break;
					}
				
					if($originalID != $aRow['OriginalRecordID'])
					{
						break;
					}
					$i++;
				}
				reset($rowArray);
				for($j=0;$j<$i;$j++)$aRow=next($rowArray);
				$aRow['Type']="Modified";
				array_push($resultArray,$aRow);
	
			}
		}
		if(($aRow=next($rowArray))===FALSE)
		{
	//		echo "No more to do<br>\n";
			break;
		}
		$i++;
	}
/*	
	echo "<br>\n";
	
	foreach($resultArray as $row)
	{
		echo $row['EventID']." ".$row['OriginalRecordID']." ".$row['Start']." ".$row['Type']."<br>\n";
	}
*/	
	
	usort($resultArray,"compare1");
	
	$style = "";
//	$text = "";
//	$strDate = "";
	
	foreach($resultArray as $resultRow)
	{
	
	//	echo $resultRow['DeletedBy']."\n";
		if($resultRow['Type']=="Deleted")
		{	
//			$date=new datetime($resultRow['DeletedWhen']);
//			$strDate = $date->format("d/m/y");
//			$actor = str_replace(' ','&nbsp;',$array[$resultRow['DeletedBy']]);
//			$text = "Deleted&nbsp;by:&nbsp;".$actor;
			$style = " style=\"color:red;\" ";
		}
		elseif($resultRow['Type']=="New")
		{	
//			$date=new datetime($resultRow['CreatedWhen']);
//			$strDate = $date->format("d/m/y");
//			$actor = str_replace(' ','&nbsp;',$array[$resultRow['CreatedBy']]);
//			$text = "Created&nbsp;by:&nbsp;".$actor;
			$style = " style=\"color:green;\" ";
		}
		elseif($resultRow['Type']=="Old")
		{	
//			$date=new datetime($resultRow['CreatedWhen']);
//			$strDate = $date->format("d/m/y");
//			$actor = str_replace(' ','&nbsp;',$array[$resultRow['CreatedBy']]);
//			$text = "Created&nbsp;by:&nbsp;".$actor;
			$style = " style=\"color:black;\" ";
		}
		elseif($resultRow['Type']=="Modified")
		{	
//			$date=new datetime($resultRow['CreatedWhen']);
//			$strDate = $date->format("d/m/y");
//			$actor = str_replace(' ','&nbsp;',$array[$resultRow['CreatedBy']]);
//			$text = "Modified&nbsp;by:&nbsp;".$actor;
			$style = " style=\"color:orange;\" ";
		}
		
		
		$start = new datetime($resultRow['Start']);
		if($start->format("Y")!=$previousDate->format("Y"))$messageHTML .= printYear($start);
		if($start->format("Ym")!=$previousDate->format("Ym"))$messageHTML .= printMonth($start);
		if($start->format("Ymd")!=$previousDate->format("Ymd"))$messageHTML .= printDay($start);
		$previousDate = $start;
		
		
		$startString = $start->format("H:i");
		
		$end = new datetime($resultRow['End']);
		$endString = $end->format("H:i");
		
		//	$messageHTML .= "<tr><td width=\"20px\"></td><td".$style.">".$startString."</td><td".$style.">".$resultRow['EventID']."</td><td".$style.">".$resultRow['OriginalRecordID']."</td><td".$style.">".$resultRow['Title']."</td><td".$style.">".$resultRow['Description']."</td><td".$style.">".$text."&nbsp;".$strDate."</td></tr>\n";
		$messageHTML .= "<tr><td width=\"20px\"></td><td".$style.">".$startString."&#8209;".$endString."</td><td".$style.">".$resultRow['Title']."</td><td".$style.">".$resultRow['Description']."</td></tr>\n";
	}
	$messageHTML .= "</table>\n";
	//echo $messageHTML."<br>";
	
	$message = new Message();
	
/*	$sql = "SELECT Email FROM SnapUsers";
	$database->ExecuteSQL($sql);
	
	while($row = $database->getRow())
	{
		if($row['Email']!="")$message->addRecipient($row['Email']);
//		echo $row['Email']."<br>\n";
	}
*/
	$addresses = getAddresses($group);
	foreach($addresses as $address)
	{
		$message->addRecipient($address);
//		echo $group."  ".$address."<br>\n";
	}
	
	//$message->addRecipient("michael@michaelstoddart.org");
	//$message->addRecipient("directorofmusic@stnicnewcastle.co.uk");
	$message->setSubject($groupsarray[$group]." Choir Schedule as on ".$dtNow->format("F j, Y"));
	$message->setFrom("Cathedral Choir Admin<no-reply@stnicholascathedralmusic.co.uk>");
	$message->addStyle("p, td {font-family: sans-serif;vertical-align:top;font-size: 70%;color:#003366;}");
	$message->addStyle("h1{font-family: sans-serif;font-size: 100%;color:#003366;}");
	$message->addStyle("h2{font-family: sans-serif;font-size: 80%;color:#003366;}");
	$message->addStyle("td.EventReportYear{font-family: sans-serif;font-size: 120%;font-weight: 400; color:#003366;}");
	$message->addStyle("td.EventReportMonth{font-family: sans-serif;font-size: 110%;font-weight: 400; color:#003366;}");
	$message->addStyle("td.EventReportDay{font-family: sans-serif;font-size: 80%;font-weight: 400; color:#003366;}");
	$message->setMessageBody($messageHTML);
	$message->send();

}

//echo dirname(__FILE__)."\n";

$topdirectory=dirname(__FILE__)."/../../MSWebGUI";
include $topdirectory.'/FileList.php';

$topdirectory=dirname(__FILE__)."/..";
include $topdirectory.'/FileList.php';


$xml = simplexml_load_file(dirname(__FILE__).'/../config/config.xml');
$databaseXML = $xml->Database;
$database = new Database($databaseXML->Server,$databaseXML->Database,$databaseXML->Username,$databaseXML->Password);

//echo "hello";
mailGroup($database, '1');
mailGroup($database, '2');
mailGroup($database, '3');

?>